package vn.edu.saigontech.ncms.web.teacher;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFTextbox;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import vn.edu.saigontech.ncms.service.OfferingService;
import vn.edu.saigontech.ncms.service.ParameterService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;
import vn.edu.saigontech.ncms.web.teacher.report.OfferingTeachingDetail;
import vn.edu.saigontech.ncms.web.teacher.report.TeacherTeachingDetail;
import vn.edu.saigontech.ncms.web.teacher.report.TeachingHourReport;
import vn.edu.saigontech.ncms.web.teacher.report.haftmonth.OfferingTeachingDetailGroupRow;
import vn.edu.saigontech.ncms.web.teacher.report.haftmonth.TeacherTeachingDetailRow;
import vn.edu.saigontech.ncms.web.teacher.report.haftmonth.TeachingClassesHalfMonthReport;
import vn.edu.saigontech.ncms.web.teacher.report.haftmonth.TeachingHourHalfMonthReport;

@SuppressWarnings("serial")
public class TeacherReportAction extends BaseAction {
	private static final int halfMonthDate = 15;
	
	private String fromDate;
	private String toDate;
	
	public String showTeachingHourReport() {
		this.fromDate = DateUtil.toString(DateUtil.startOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		this.toDate = DateUtil.toString(DateUtil.endOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		return SUCCESS;
	}
	
	public String showTeachingClassesReport() {
		Date currentDate = DateUtil.newDate();
		// report from 1st to 15th of the month
		if (DateUtil.getDayOfMonth(currentDate) <= halfMonthDate) {
			this.fromDate = DateUtil.toString(DateUtil.startOfMonth(currentDate), this.i18n("format.date"));
			this.toDate = DateUtil.toString(DateUtil.newDate(halfMonthDate, 
							DateUtil.getMonth(currentDate) + 1, 
							DateUtil.getYear(currentDate)), this.i18n("format.date"));
		}
		// report from 16th to the end of the month
		else {
			this.fromDate = DateUtil.toString(DateUtil.newDate(halfMonthDate + 1, 
					DateUtil.getMonth(currentDate) + 1, 
					DateUtil.getYear(currentDate)), this.i18n("format.date"));
			this.toDate = DateUtil.toString(DateUtil.endOfMonth(currentDate), this.i18n("format.date"));
			
		}
		return SUCCESS;
	}
	
	public String doTeachingClassesReportToExcel() {
		TeachingClassesHalfMonthReport report = new TeachingClassesHalfMonthReport(
			DateUtil.fromString(this.fromDate, this.i18n("format.date")), 
			DateUtil.fromString(this.toDate, this.i18n("format.date")));
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "TeachingClassesReport-" + this.fromDate + "-" + this.toDate + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();

			HSSFFont font13b 	= this.makeFont(workbook, "Times New Roman", 13, true);
			HSSFFont font12b 	= this.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont font12 	= this.makeFont(workbook, "Times New Roman", 12, false);
			
			HSSFCellStyle font13bCenter = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font13b);
			HSSFCellStyle font12bCenter = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font12b);
			HSSFCellStyle font12Center 	= this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font12);
			HSSFCellStyle font12Left 	= this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font12);
			
			final int lastColumn = 11;
			
			/* by class sheet */
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFRow row = sheet.createRow(0);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font13bCenter);
			cell.setCellValue(new HSSFRichTextString("BẢNG THEO DÕI CÁC LỚP GIẢNG DẠY CỦA GIÁO VIÊN CHƯƠNG TRÌNH TIẾNG ANH BUỔI TỐI"));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastColumn));
			row.setHeightInPoints(25);

			row = sheet.createRow(1);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font13bCenter);		
			cell.setCellValue(new HSSFRichTextString(String.format("Từ %s Đến %s", this.fromDate, this.toDate)));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, lastColumn));
			row.setHeightInPoints(25);
			
			row = sheet.createRow(2);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font13bCenter);
			cell.setCellValue(new HSSFRichTextString(String.format("Cơ Sở: Quang Trung")));
			sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, lastColumn));
			row.setHeightInPoints(25);
			
			/* HEADINGs */
			row = sheet.createRow(4);
			this.makeColumnLabel(workbook, sheet, row, 0, "Stt", 4*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 0, 0));
			this.makeColumnLabel(workbook, sheet, row, 1, "Tên giáo viên", 17*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 2, "", 12*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 1, 2));
			this.makeColumnLabel(workbook, sheet, row, 3, "Lớp", 13*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 4, "", 13*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 4, 3, 4));
			this.makeColumnLabel(workbook, sheet, row, 5, "Số giờ được thanh toán", 13*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 5, 5));
			this.makeColumnLabel(workbook, sheet, row, 6, "Ngày KG - KT", 12*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 7, "", 2*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 8, "", 12*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 6, 8));
			this.makeColumnLabel(workbook, sheet, row, 9, "Ngày tổng kết nửa đầu khóa", 13*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 9, 9));
			this.makeColumnLabel(workbook, sheet, row, 10, "Ngày tổng kết nửa cuối khóa", 13*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 10, 10));
			this.makeColumnLabel(workbook, sheet, row, 11, "Ghi chú", 16*256, font12bCenter);
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 11, 11));
			row.setHeightInPoints(45);

			row = sheet.createRow(5);
			this.makeColumnLabel(workbook, sheet, row, 0, "", 4*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 1, "", 17*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 2, "", 12*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 3, "Được thanh toán trong kỳ", 13*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 4, "Không được thanh toán trong kỳ", 13*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 5, "", 13*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 6, "", 12*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 7, "", 2*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 8, "", 12*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 9, "", 13*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 10, "", 13*256, font12bCenter);
			this.makeColumnLabel(workbook, sheet, row, 11, "", 16*256, font12bCenter);
			row.setHeightInPoints(51);
			
			int r = 6;
			
			for (int i = 0; i < report.getTeachings().size(); i++) {
				TeacherTeachingDetail teachingDetail = report.getTeachings().get(i);
				row = sheet.createRow(r);

				// STT
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(i + 1);
				cell.setCellStyle(font12Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size(), 0, 0));
				
				// Instructor
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(teachingDetail.getLastName() 
						+ (teachingDetail.getMidName() != null ? " " + teachingDetail.getMidName():"")));
				cell.setCellStyle(font12Left);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size(), 1, 1));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(teachingDetail.getFirstName()));
				cell.setCellStyle(font12Left);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size(), 2, 2));
				
				ArrayList<OfferingTeachingDetail> offeringDetails = 
					new ArrayList<OfferingTeachingDetail>(teachingDetail.getDetails());
				
				int firstRow = r;
				
				for(int j = 0; j < offeringDetails.size(); j++, r++) {
					OfferingTeachingDetail offeringDetail = offeringDetails.get(j);
					if(j != 0) {
						row = sheet.createRow(r);
					}
					
					if (offeringDetail.isHasTestDate()) {
						cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
						cell.setCellStyle(font12bCenter);
						cell.setCellValue(new HSSFRichTextString(offeringDetail.getOffering().getName()));

						cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellStyle(font12Center);
						cell.setCellValue(offeringDetail.getHoursPerSession() * offeringDetail.getSessions());
					}
					else {						
						cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
						cell.setCellStyle(font12Center);
						cell.setCellValue(new HSSFRichTextString(offeringDetail.getOffering().getName()));
					}
					
					cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(font12Center);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getOffering().getStartDate(), this.i18n("format.date"))));
					
					cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font12Center);
					cell.setCellValue(new HSSFRichTextString(String.format("-")));
					
					cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font12Center);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getOffering().getEndDate(), this.i18n("format.date"))));
					
					cell = row.createCell(9, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(""));
					
					cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(""));
					
					cell = row.createCell(11, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(""));
					
					row.setHeightInPoints(18);
				}
				
				row = sheet.createRow(r);
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(font12bCenter);
				cell.setCellValue(new HSSFRichTextString("Tổng số giờ được thanh toán"));
				sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(font12bCenter);
				cell.setCellFormula("SUM(F" + (firstRow + 1) + ":"  + "F" + r + ")");
				sheet.addMergedRegion(new CellRangeAddress(r, r, 5, lastColumn));
				
				row.setHeightInPoints(35);
				r++;
			}
						
			row = sheet.createRow(r++);
			row.setHeightInPoints(25);
			
			
			/* misc */
			sheet.getPrintSetup().setLandscape(true);
			sheet.setMargin(HSSFSheet.TopMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.BottomMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.LeftMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.RightMargin, (double) .50);
			sheet.getPrintSetup().setScale((short)82);
			sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
			workbook.setRepeatingRowsAndColumns(0, -1, -1, 6, 8);

			workbook.write(RESPONSE().getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.addLoggingDetail(BaseAction.REPORT, "logging.teaching.classes.excel.testdate", fromDate, toDate);
		return NONE;
	}
	
	public String showTeachingHourReportByTestDate() {
		Date currentDate = DateUtil.newDate();
		// report from 1st to 15th of the month
		if (DateUtil.getDayOfMonth(currentDate) <= halfMonthDate) {
			this.fromDate = DateUtil.toString(DateUtil.startOfMonth(currentDate), this.i18n("format.date"));
			this.toDate = DateUtil.toString(DateUtil.newDate(halfMonthDate, 
							DateUtil.getMonth(currentDate) + 1, 
							DateUtil.getYear(currentDate)), this.i18n("format.date"));
		}
		// report from 16th to the end of the month
		else {
			this.fromDate = DateUtil.toString(DateUtil.newDate(halfMonthDate + 1, 
					DateUtil.getMonth(currentDate) + 1, 
					DateUtil.getYear(currentDate)), this.i18n("format.date"));
			this.toDate = DateUtil.toString(DateUtil.endOfMonth(currentDate), this.i18n("format.date"));
			
		}
		return SUCCESS;
	}
	
	public String doTeachingHourReportByTestDateToExcel() {
		HSSFWorkbook wb = ExcelUtil.getTemplate("teaching-hour-report.xls");
		if (Checker.isNull(wb)) return NONE;
		
		TeachingHourHalfMonthReport report = new TeachingHourHalfMonthReport(
			DateUtil.fromString(this.fromDate, this.i18n("format.date")), 
			DateUtil.fromString(this.toDate, this.i18n("format.date")));
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "TeachingHourReport-" + this.fromDate + "-" + this.toDate + ".xls\"");
		
		try {
			HSSFFont font13b 	= this.makeFont(wb, "Times New Roman", 13, true);
			HSSFFont font11b 	= this.makeFont(wb, "Times New Roman", 11, true);
			HSSFFont font11 	= this.makeFont(wb, "Times New Roman", 11, false);
			HSSFFont font10 	= this.makeFont(wb, "Times New Roman", 10, false);
			
			HSSFCellStyle font13bCenter = this.makeCellStyle(wb, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font13b);
			HSSFCellStyle font11bCenter = this.makeCellStyle(wb, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11b);
			HSSFCellStyle font11bLeft 	= this.makeCellStyle(wb, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font11b);
			HSSFCellStyle font11Center 	= this.makeCellStyle(wb, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle font11Left 	= this.makeCellStyle(wb, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font11);
			
			/* by class sheet */
			HSSFSheet sheet = wb.getSheetAt(0);
			
			HSSFRow row = sheet.getRow(1);
			HSSFCell cell = row.getCell(0);		
			cell.setCellValue(new HSSFRichTextString(String.format("Từ %s Đến %s", this.fromDate, this.toDate)));
			
			/* FIRST LIST */			
			int r = 10;
			int midFirstRow = r + 1;
			for (int i = 0; i < report.getMidOfferingRows().size(); i++) {
				OfferingTeachingDetailGroupRow offeringDetail = report.getMidOfferingRows().get(i);
				row = sheet.createRow(r);

				// STT
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(i + 1);
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 0, 0));
				
				// Instructor
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(offeringDetail.getName()));
				cell.setCellStyle(font11bCenter);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 1, 1));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getStartDate(), this.i18n("format.date"))));
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 2, 2));
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("-"));
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 3, 3));
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getEndDate(), this.i18n("format.date"))));
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 4, 4));
				
				cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(offeringDetail.getTotalHours());
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 7, 7));
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellFormula("N" + (r + 1));
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 8, 8));
				
				cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellFormula("H" + (r + 1) + "-I" + (r + 1));
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 9, 9));
				
				// Total teaching hours
				cell = row.createCell(13, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(font11Center);
				cell.setCellFormula("SUM(M" + (r + 1) + ":"  + "M" + (r + offeringDetail.getTeacherDetailRows().size()) + ")");
				sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 13, 13));
				
				for (int k = 14; k <= 19; k++) {
					cell = row.createCell(k, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(""));
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, k, k));
				}
				
				LinkedList<TeacherTeachingDetailRow> teacherDetails = 
					new LinkedList<TeacherTeachingDetailRow>(offeringDetail.getTeacherDetailRows());
				
				for(int j = 0; j < teacherDetails.size(); j++, r++) {
					TeacherTeachingDetailRow teacherDetail = teacherDetails.get(j);
					if(j != 0) {
						row = sheet.createRow(r);
					}
					
					cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(font11Left);
					cell.setCellValue(new HSSFRichTextString(teacherDetail.getLastName() 
							+ (teacherDetail.getMidName() != null ? " " + teacherDetail.getMidName():"")));
					
					cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(font11Left);
					cell.setCellValue(new HSSFRichTextString(teacherDetail.getFirstName()));
					
					cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellValue(OfferingService.HOURS_PER_SESSION);
					
					cell = row.createCell(11, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(font11Center);
					cell.setCellValue(teacherDetail.getTotalSessions());
					
					cell = row.createCell(12, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellFormula("L" + (r + 1) + "*" + "K" + (r + 1));
					
					row.setHeightInPoints(25);
				}
			}
			
			int midSum = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(25);
			this.makeColumnLabel(wb, sheet, row, 0, "TỔNG CỘNG NỬA ĐẦU KHÓA", 4*256, font11bLeft);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11Center);
			cell.setCellFormula("SUM(H" + midFirstRow + ":"  + "H" + r + ")");
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(I" + midFirstRow + ":"  + "I" + r + ")");
			
			cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11Center);
			cell.setCellFormula("SUM(J" + midFirstRow + ":"  + "J" + r + ")");
			
			cell = row.createCell(13, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(N" + midFirstRow + ":"  + "N" + r + ")");
			
			
			/* SECOND LIST */
			r++;
			row = sheet.createRow(r);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font13bCenter);
			cell.setCellValue(new HSSFRichTextString(String.format("NỬA CUỐI KHÓA")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 19));
			row.setHeightInPoints(25);
			
			r++;
			int finalFirstRow = r + 1;
			for (int i = 0; i < report.getFinalOfferingRows().size(); i++) {
				OfferingTeachingDetailGroupRow offeringDetail = report.getFinalOfferingRows().get(i);
				row = sheet.createRow(r);

				// STT
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(i + 1);
				cell.setCellStyle(font11Center);
				try {
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 0, 0));
								
					// Instructor
					cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(offeringDetail.getName()));
					cell.setCellStyle(font11bCenter);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 1, 1));
					
					cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getStartDate(), this.i18n("format.date"))));
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 2, 2));
					
					cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString("-"));
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 3, 3));
					
					cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getEndDate(), this.i18n("format.date"))));
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 4, 4));
					
					cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellValue(offeringDetail.getTotalHours());
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 7, 7));
					
					cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellFormula("N" + (r + 1));
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 8, 8));
					
					cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellFormula("R" + (r + 1));
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 9, 9));
					
					cell = row.createCell(13, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellFormula("H" + (r + 1) + "-R" + (r + 1));
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 13, 13));
					
					// Total teaching hours
					cell = row.createCell(17, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11bCenter);
					cell.setCellFormula("SUM(Q" + (r + 1) + ":"  + "Q" + (r + offeringDetail.getTeacherDetailRows().size()) + ")");
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 17, 17));
					
					cell = row.createCell(18, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellFormula("N" + (r + 1) + "+R" + (r + 1));
					cell.setCellStyle(font11Center);
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 18, 18));
					
					for (int k = 10; k <= 12; k++) {
						cell = row.createCell(k, HSSFCell.CELL_TYPE_STRING);
						cell.setCellValue(new HSSFRichTextString(""));
						sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, k, k));
					}
					
					cell = row.createCell(19, HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(""));
					sheet.addMergedRegion(new CellRangeAddress(r, r + offeringDetail.getTeacherDetailRows().size() - 1, 19, 19));
					
					LinkedList<TeacherTeachingDetailRow> teacherDetails = 
						new LinkedList<TeacherTeachingDetailRow>(offeringDetail.getTeacherDetailRows());
					
					for(int j = 0; j < teacherDetails.size(); j++, r++) {
						TeacherTeachingDetailRow teacherDetail = teacherDetails.get(j);
						if(j != 0) {
							row = sheet.createRow(r);
						}
						
						cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
						cell.setCellStyle(font11Left);
						cell.setCellValue(new HSSFRichTextString(teacherDetail.getLastName() 
								+ (teacherDetail.getMidName() != null ? " " + teacherDetail.getMidName():"")));
						
						cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
						cell.setCellStyle(font11Left);
						cell.setCellValue(new HSSFRichTextString(teacherDetail.getFirstName()));
						
						cell = row.createCell(14, HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellStyle(font11Center);
						cell.setCellValue(OfferingService.HOURS_PER_SESSION);
						
						cell = row.createCell(15, HSSFCell.CELL_TYPE_STRING);
						cell.setCellStyle(font11Center);
						cell.setCellValue(teacherDetail.getTotalSessions());
						
						cell = row.createCell(16, HSSFCell.CELL_TYPE_NUMERIC);
						cell.setCellStyle(font11Center);
						cell.setCellFormula("P" + (r + 1) + "*" + "O" + (r + 1));
						
						row.setHeightInPoints(25);
					}
				} catch (Exception ex) {
					System.out.println("Something go wrong: " + ex.getMessage());
				}
			}
			
			int finalSum = r + 1;
			
			row = sheet.createRow(r);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11bLeft);
			cell.setCellValue(new HSSFRichTextString("TỔNG CỘNG NỬA CUỐI KHÓA"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 6));
			row.setHeightInPoints(30);
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(H" + finalFirstRow + ":"  + "H" + r + ")");
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11Center);
			cell.setCellFormula("SUM(I" + finalFirstRow + ":"  + "I" + r + ")");
			
			cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(J" + finalFirstRow + ":"  + "J" + r + ")");
			
			cell = row.createCell(13, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11Center);
			cell.setCellFormula("SUM(N" + finalFirstRow + ":"  + "N" + r + ")");
			
			cell = row.createCell(17, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(R" + finalFirstRow + ":"  + "R" + r + ")");
			
			cell = row.createCell(18, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(S" + finalFirstRow + ":"  + "S" + r + ")");
			
			row = sheet.createRow(++r);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11bLeft);
			cell.setCellValue(new HSSFRichTextString("TỔNG CỘNG (1) + (2)"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 16));
			row.setHeightInPoints(30);
			
			cell = row.createCell(17, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(N" + midSum + ","  + "R" + finalSum + ")");
			sheet.addMergedRegion(new CellRangeAddress(r, r, 17, 18));
			
			/* Text-boxes */
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
			HSSFRichTextString text = new HSSFRichTextString("(1)");
			text.applyFont(font10);
			HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor(0,0,0,0,(short)13,midSum-1,(short)14,midSum));
			textbox.setLineStyle(HSSFTextbox.LINESTYLE_NONE);
			textbox.setNoFill(true);
			textbox.setHorizontalAlignment(HSSFCellStyle.ALIGN_RIGHT);
			textbox.setString(text);
			
			text = new HSSFRichTextString("(2)");
			text.applyFont(font10);
			textbox = patriarch.createTextbox(new HSSFClientAnchor(0,0,0,0,(short)17,finalSum-1,(short)18,finalSum));
			textbox.setLineStyle(HSSFTextbox.LINESTYLE_NONE);
			textbox.setNoFill(true);
			textbox.setHorizontalAlignment(HSSFCellStyle.ALIGN_RIGHT);
			textbox.setString(text);
			
			/* by class sheet */
			sheet = wb.getSheetAt(1);
			
			row = sheet.getRow(0);
			cell = row.getCell(0);
			String initString = cell.getStringCellValue();
			cell.setCellValue(new HSSFRichTextString(initString + " " + String.format("TỪ %s ĐẾN %s", this.fromDate, this.toDate)));
			row.setHeightInPoints(25);
			
			r = 3;
			int firstRow = r + 1;
			for (int i = 0; i < report.getTeachings().size(); i++) {
				TeacherTeachingDetail teachingDetail = report.getTeachings().get(i);
				row = sheet.createRow(r);

				// STT
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(i + 1);
				cell.setCellStyle(font11Center);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 0, 0));
				
				// Instructor
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(teachingDetail.getLastName() 
						+ (teachingDetail.getMidName() != null ? " " + teachingDetail.getMidName():"")));
				
				cell.setCellStyle(font11Left);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 1, 1));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(teachingDetail.getFirstName()));
				cell.setCellStyle(font11Left);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 2, 2));
				
				// Total teaching hours
				cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(font11Center);
				cell.setCellFormula("SUM(K" + (r + 1) + ":"  + "K" + (r + teachingDetail.getDetails().size()) + ")");
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 11, 11));
				
				ArrayList<OfferingTeachingDetail> offeringDetails = 
					new ArrayList<OfferingTeachingDetail>(teachingDetail.getDetails());
				
				for(int j = 0; j < offeringDetails.size(); j++, r++) {
					OfferingTeachingDetail offeringDetail = offeringDetails.get(j);
					if(j != 0) {
						row = sheet.createRow(r);
					}
					
					cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(font11Center);
					cell.setCellValue(new HSSFRichTextString(offeringDetail.getOffering().getName()));
					
					cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(font11Center);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getOffering().getStartDate(), this.i18n("format.date"))));
					
					cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellValue(new HSSFRichTextString(String.format("-")));
					
					cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getOffering().getEndDate(), this.i18n("format.date"))));
					
					cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellValue(offeringDetail.getAdvanceHours());
					
					cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellValue(offeringDetail.getHoursPerSession());
					
					cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellValue(offeringDetail.getSessions());
					
					cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(font11Center);
					cell.setCellFormula("J" + (r + 1) + "*" + "I" + (r + 1));
					
					cell = row.createCell(12, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(font11Left);
					cell.setCellValue(new HSSFRichTextString(offeringDetail.getComment()));
					
					row.setHeightInPoints(25);
				}
			}
						
			row = sheet.createRow(r);
			row.setHeightInPoints(25);
			
			this.makeColumnLabel(wb, sheet, row, 0, "TỔNG CỘNG", 8*256, font11bCenter);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(font11bCenter);
			cell.setCellFormula("SUM(L" + firstRow + ":"  + "L" + r + ")");
						
			/* signature */
			r += 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(15);
			
			Date now = new Date();
			cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);			
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(String.format("Tp, Hồ Chí Minh, Ngày %d Tháng %d Năm %d", 
					DateUtil.getDayOfMonth(now),
					DateUtil.getMonth(now) + 1,
					DateUtil.getYear(now))));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 10, 12));
			
			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(15);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(String.format("NGƯỜI LẬP BẢNG")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 1));
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(String.format("TRƯỞNG BỘ PHẬN")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(String.format("GIÁM ĐỐC HỌC VỤ")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 10));
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(String.format("HIỆU TRƯỞNG")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
			
			r = r + 5;
			row = sheet.createRow(r);
			row.setHeightInPoints(15);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("THR").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 1));
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("LEP").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("MAN").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 10));
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(font11Center);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("PCL").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
			
			/* misc */
			sheet.getPrintSetup().setLandscape(true);
			sheet.setMargin(HSSFSheet.TopMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.BottomMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.LeftMargin, (double) .50);
	        sheet.setMargin(HSSFSheet.RightMargin, (double) .50);
			sheet.getPrintSetup().setScale((short)88);
			sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
			wb.setRepeatingRowsAndColumns(1, -1, -1, 2, 2);

			wb.write(RESPONSE().getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.addLoggingDetail(BaseAction.REPORT, "logging.teaching.excel.testdate", fromDate, toDate);
		return NONE;
	}

	public String doTeachingHourReport() {
		TeachingHourReport report = new TeachingHourReport(DateUtil.fromString(this.fromDate, this.i18n("format.date")), 
				DateUtil.fromString(this.toDate, this.i18n("format.date"))); 
		REQUEST().setAttribute("report", report);
		
		this.addLoggingDetail(BaseAction.REPORT, "logging.teaching.report");
		return SUCCESS;
	}
	
	public String doTeachingHourReportToExcel() {
		TeachingHourReport report = new TeachingHourReport(DateUtil.fromString(this.fromDate, this.i18n("format.date")), 
				DateUtil.fromString(this.toDate, this.i18n("format.date")));
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "TeachingHourReport-" + this.fromDate + "-" + this.toDate + ".xls\"");
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont font14 = this.makeFont(workbook, "Times New Roman", 14, true);
			HSSFFont font12 = this.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont font11 = this.makeFont(workbook, "Times New Roman", 11, false);
			HSSFFont font10 = this.makeFont(workbook, "Times New Roman", 10, false);
			
			HSSFCellStyle style1 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font12);
			HSSFCellStyle style2 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font12);
			HSSFCellStyle style3 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style4 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style5 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style6 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style7 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style8 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style9 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style10 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style11 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style12 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, font11);
			HSSFCellStyle style13 = this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font10);
			
			HSSFRow row = sheet.createRow(0);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font14));
			cell.setCellValue(new HSSFRichTextString("BẢNG TÍNH GIỜ GIẢNG CỦA GIÁO VIÊN CHƯƠNG TRÌNH TIẾNG ANH TỐI"));
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 9));

			row = sheet.createRow(2);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font12));		
			cell.setCellValue(new HSSFRichTextString(String.format("Từ %s Đến %s", this.fromDate, this.toDate)));
			sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 9));
			
			row = sheet.createRow(4);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(this.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, font12));
			cell.setCellValue(new HSSFRichTextString(String.format("Cơ Sở: Quang Trung")));
			sheet.addMergedRegion(new CellRangeAddress(4, 5, 0, 9));
			
			row = sheet.createRow(6);
			
			this.makeColumnLabel(workbook, sheet, row, 0, "STT", 2000, style1);
			this.makeColumnLabel(workbook, sheet, row, 1, "TÊN GIÁO VIÊN", 7000,style1);
			this.makeColumnLabel(workbook, sheet, row, 2, "LỚP", 4000, style1);
			this.makeColumnLabel(workbook, sheet, row, 3, "Ngày KG -KT", 6500, style1);
			this.makeColumnLabel(workbook, sheet, row, 4, "Giờ \n dự kiến", 3000, style1);
			this.makeColumnLabel(workbook, sheet, row, 5, "Số giờ \n /buổi", 3000, style1);
			this.makeColumnLabel(workbook, sheet, row, 6, "Tổng \n số buổi", 3000, style1);
			this.makeColumnLabel(workbook, sheet, row, 7, "Số \n giờ giảng", 3000, style1);
			this.makeColumnLabel(workbook, sheet, row, 8, "Tổng giờ giảng", 4000, style1);
			this.makeColumnLabel(workbook, sheet, row, 9, "Ghi chú", 5000, style1);
						
			// Teaching details for Vietnamese teachers
			
			row = sheet.createRow(7);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style2);
			cell.setCellValue(new HSSFRichTextString(String.format("NGƯỜI VIỆT NAM")));
			sheet.addMergedRegion(new CellRangeAddress(7, 8, 0, 9));
			
			int r = 9;
			int no = 0;
			int vnFirstRow = r + 1;
			for (int i = 0; i < report.getVietnameseTeachings().size(); i++, no++) {
				TeacherTeachingDetail teachingDetail = report.getVietnameseTeachings().get(i);
				row = sheet.createRow(r);

				// STT
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(i + 1);
				cell.setCellStyle(style3);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 0, 0));
				
				// Instructor
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(teachingDetail.getTeacher()));
				cell.setCellStyle(style4);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 1, 1));
				
				// Total teaching hours
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(style5);
//				cell.setCellValue(teachingDetail.getTotalTeachingHours());
				cell.setCellFormula("SUM(H" + (r + 1) + ":"  + "H" + (r + teachingDetail.getDetails().size()) + ")");
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 8, 8));
				
				ArrayList<OfferingTeachingDetail> offeringDetails = new ArrayList<OfferingTeachingDetail>(teachingDetail.getDetails());
				
				for(int j = 0; j < offeringDetails.size(); j++, r++) {
					OfferingTeachingDetail offeringDetail = offeringDetails.get(j);
					if(j != 0) {
						row = sheet.createRow(r);
					}
					
					cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(style6);
					cell.setCellValue(new HSSFRichTextString(offeringDetail.getOffering().getName()));
					
					cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(style7);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getOffering().getStartDate(), this.i18n("format.date")) + " - " + DateUtil.toString(offeringDetail.getOffering().getEndDate(), this.i18n("format.date"))));
					
					cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style8);
					cell.setCellValue(offeringDetail.getAdvanceHours());
					
					cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style9);
					cell.setCellValue(offeringDetail.getHoursPerSession());
					
					cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style10);
					cell.setCellValue(offeringDetail.getSessions());
					
					cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style11);
					cell.setCellFormula("F" + (r + 1) + "*" + "G" + (r + 1));
//					cell.setCellValue(offeringDetail.getTeachingHours());
					
					cell = row.createCell(9, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(style12);
					cell.setCellValue(new HSSFRichTextString(offeringDetail.getComment()));
				}
			}
			
			int vnLastRow = r;
			
			// Teaching details for foreign teachers
			
			row = sheet.createRow(r);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style2);
			cell.setCellValue(new HSSFRichTextString(String.format("NGƯỜI NƯỚC NGOÀI")));
			sheet.addMergedRegion(new CellRangeAddress(r, ++r, 0, 9));
			
			int fgFirstRow = r + 2;
			r++;
			for (int i = 0; i < report.getForeignTeachings().size(); i++, no++) {
				TeacherTeachingDetail teachingDetail = report.getForeignTeachings().get(i);
				row = sheet.createRow(r);

				// STT
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(no + 1);
				cell.setCellStyle(style3);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 0, 0));
//				sheet.addMergedRegion(new Region(r, (short)0, r + teachingDetail.getDetails().size() - 1, (short)0));
				
				// Instructor
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(teachingDetail.getTeacher()));
				cell.setCellStyle(style4);
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 1, 1));
//				sheet.addMergedRegion(new Region(r, (short)1, r + teachingDetail.getDetails().size() - 1, (short)1));
				
				// Total teaching hours
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(style5);
//				cell.setCellValue(teachingDetail.getTotalTeachingHours());
				cell.setCellFormula("SUM(H" + (r + 1) + ":"  + "H" + (r + teachingDetail.getDetails().size()) + ")");
				sheet.addMergedRegion(new CellRangeAddress(r, r + teachingDetail.getDetails().size() - 1, 8, 8));
//				sheet.addMergedRegion(new Region(r, (short)8, r + teachingDetail.getDetails().size() - 1, (short)8));
				
				ArrayList<OfferingTeachingDetail> offeringDetails = new ArrayList<OfferingTeachingDetail>(teachingDetail.getDetails());
				for(int j = 0; j < offeringDetails.size(); j++, r++) {
					OfferingTeachingDetail offeringDetail = offeringDetails.get(j);
					if(j != 0) {
						row = sheet.createRow(r);
					}					
					cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(style6);
					cell.setCellValue(new HSSFRichTextString(offeringDetail.getOffering().getName()));
					
					
					cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(style7);
					cell.setCellValue(new HSSFRichTextString(DateUtil.toString(offeringDetail.getOffering().getStartDate(), this.i18n("format.date")) + " - " + DateUtil.toString(offeringDetail.getOffering().getEndDate(), this.i18n("format.date"))));
					
					cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style8);
					cell.setCellValue(offeringDetail.getAdvanceHours());
					
					cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style9);
					cell.setCellValue(offeringDetail.getHoursPerSession());
					
					cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style10);
					cell.setCellValue(offeringDetail.getSessions());
					
					cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(style11);
					cell.setCellValue(offeringDetail.getTeachingHours());
					
					cell = row.createCell(9, HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(style12);
					cell.setCellValue(new HSSFRichTextString(offeringDetail.getComment()));
				}
			}
			
			int fgLastRow = r;
			
			row = sheet.createRow(r);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style1);
			cell.setCellValue(new HSSFRichTextString(String.format("TỔNG CỘNG")));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 5));
//			sheet.addMergedRegion(new Region(r, (short)0, r+1, (short)5));
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(style5);
//			cell.setCellValue(report.getTotalSessions());
			cell.setCellFormula("SUM(G" + vnFirstRow + ":"  + "G" + vnLastRow + ") + " + "SUM(G" + fgFirstRow + ":"  + "G" + fgLastRow + ")");
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 6, 6));
//			sheet.addMergedRegion(new Region(r, (short)6, r+1, (short)6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(style5);
//			cell.setCellValue(report.getTotalTeachingHours());
			cell.setCellFormula("SUM(H" + vnFirstRow + ":"  + "H" + vnLastRow + ") + " + "SUM(H" + fgFirstRow + ":"  + "H" + fgLastRow + ")");
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 7, 7));
//			sheet.addMergedRegion(new Region(r, (short)7, r+1, (short)7));
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(style5);
//			cell.setCellValue(report.getTotalTeachingHours());
			cell.setCellFormula("SUM(I" + vnFirstRow + ":"  + "I" + vnLastRow + ") + " + "SUM(I" + fgFirstRow + ":"  + "I" + fgLastRow + ")");
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 8, 8));
//			sheet.addMergedRegion(new Region(r, (short)8, r+1, (short)8));
			
			r = r + 3;
			
			row = sheet.createRow(r);
			
			Date now = new Date();
			cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(String.format("Tp, Hồ Chí Minh, Ngày %d Tháng %d Năm %d", 
					DateUtil.getDayOfMonth(now),
					DateUtil.getMonth(now) + 1,
					DateUtil.getYear(now))));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 9));
//			sheet.addMergedRegion(new Region(r, (short)7, r, (short)9));
			
			r = r + 2;
			
			row = sheet.createRow(r);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(String.format("NGƯỜI LẬP BẢNG")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 1));
//			sheet.addMergedRegion(new Region(r, (short)0, r, (short)1));
			
			cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(String.format("TRƯỞNG BỘ PHẬN")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 2, 3));
//			sheet.addMergedRegion(new Region(r, (short)2, r, (short)3));
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(String.format("GIÁM ĐỐC HỌC VỤ")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 7));
//			sheet.addMergedRegion(new Region(r, (short)4, r, (short)7));
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(String.format("HIỆU TRƯỞNG")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 8, 9));
//			sheet.addMergedRegion(new Region(r, (short)8, r, (short)9));
			
			r = r + 5;
			
			row = sheet.createRow(r);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("THR").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 1));
//			sheet.addMergedRegion(new Region(r, (short)0, r, (short)1));
			
			cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("LEP").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 2, 3));
//			sheet.addMergedRegion(new Region(r, (short)2, r, (short)3));
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("MAN").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 7));
//			sheet.addMergedRegion(new Region(r, (short)4, r, (short)7));
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style13);
			cell.setCellValue(new HSSFRichTextString(ParameterService.INSTANCE.findText("PCL").toUpperCase()));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 8, 9));
//			sheet.addMergedRegion(new Region(r, (short)8, r, (short)9));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.addLoggingDetail(BaseAction.REPORT, "logging.teaching.excel");
		return NONE;
	}
	
	private void makeColumnLabel(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row, int index, String label, int width, HSSFCellStyle style) {
		HSSFCell cell = row.createCell(index, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(style);
		cell.setCellValue(new HSSFRichTextString(label));
		sheet.setColumnWidth(index, (short)width);
	}

	private HSSFCellStyle makeCellStyle(HSSFWorkbook workbook, int halign, int valign, HSSFFont font) {
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment((short) halign);
		style.setVerticalAlignment((short) valign);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}
	
	private HSSFFont makeFont(HSSFWorkbook workbook, String name, int size, boolean isBold) {
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short)size);
		font.setFontName(name);
		if(isBold) {
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		}
		return font;
	}

	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}
}